package com.roc.service.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.roc.newPage.IPageDao;
import com.roc.pojo.AccountingStatistics;
import com.roc.service.AccountingStatisticsService;
import com.roc.service.ServiceException;
import com.roc.util.Constance;
import com.roc.util.constant.ConfigProperties;
@Service
public class AccountingStatisticsServiceImpl implements AccountingStatisticsService{
	
	private Logger logger=LogManager.getLogger(AccountingStatisticsServiceImpl.class);
	@Autowired
	private IPageDao pageDao;

	@Transactional(propagation=Propagation.REQUIRED,readOnly=true)
	@Override
	public List<AccountingStatistics> queryAccountStatisticsList(
			String startDT,String mchtCd,String endDT,String dateFlag, int startRow,
			int maxRow) throws ServiceException {
		List<AccountingStatistics> accountingList = new ArrayList<AccountingStatistics>();
		List<Object> params = new ArrayList<Object>();
		try {
			
			if(!"1".equals(dateFlag) && !"2".equals(dateFlag)){
				logger.info("占比统计日期标志位值错误");
				return accountingList;
			}
			StringBuffer sb2 = new StringBuffer();
			String sql="";
			if(!"1".equals(dateFlag)){
			sql = "select t.trans_dt,    " 
							+"tii.ins_name as rout_industry_insid_cd,   "
							+"t.ext1_name,                "
							+"t.prov_cd,                  "
							+"t.city_cd,                  "
							+"t.mcht_cd,                  "
							+"t.appl_date,                "
							+"t.status,                   "
							+"t.credit_rate,              "
							+"t.brush_rate,               "
							+"t.business_rate,            "
							+"t.high_return_cnt,          "
							+"t.high_risk_return_rate,    "
							+"t.cnt_success,              "
							+"t.cnt_success_amt,          "
							+"t.cnt_success_rate,         "
							+"t.cnt_fail,                 "
							+"t.cnt_failed_rate,          "
							+"t.night_success_cnt,        "
							+"t.night_success_amt,        "
							+"t.night_failed_cnt,         "
							+"t.night_cnt,                "
							+"t.night_failed_rate,        "
							+"t.balance_query_failed_cnt, "
							+"t.balance_query_cnt,        "
							+"t.balance_query_failed_rate "
					+ "from TBL_ACCOUNTING_STATISTICS_INFO t left join tbl_ins_inf tii on tii.ins_id_cd=trim(t.rout_industry_insid_cd) where t.trans_dt >= ? and t.trans_dt<= ?";
			StringBuffer sb1 = new StringBuffer(sql);
			params.add(startDT);
			params.add(endDT);
			if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
				sb1.append("and t.MCHNT_CD =? ");
				params.add(mchtCd);
			}
			sb2=sb1;
			}else{
				 sql = "SELECT CASE WHEN T.T1_TRANS_DT IS NOT NULL THEN T.T1_TRANS_DT ELSE T.T2_TRANS_DT END, "
							+ "CASE WHEN T.T1_ROUT_INDUSTRY_INS_ID_CD IS NOT NULL THEN "
						    +" (select tii.ins_name from tbl_ins_inf tii where tii.ins_id_cd=trim(T.T1_ROUT_INDUSTRY_INS_ID_CD)) ELSE"
							+" (select tii2.ins_name from tbl_ins_inf tii2 where tii2.ins_id_cd=trim(T.T2_ROUT_INDUSTRY_INS_ID_CD)) END,"
							+ "T5.REAL_NAME ,T.PROV_CD ,T.CITY_CD ,T.MCHT_CD ,T.APPL_DATE,T.STATUS , "
							+ "CASE WHEN T.M > 0 THEN ROUND((T.M / T.O) * 100, 2) || '%' ELSE '0.00%' END , "
							+ "CASE WHEN T.N > 0 THEN ROUND((T.N / T.O) * 100, 2) || '%' ELSE '0.00%' END , "
							+ "CASE WHEN T.O > 0 THEN T.O ELSE 0 END ,CASE WHEN T.P > 0 THEN T.P ELSE 0 END , "
							+ "CASE WHEN T.P > 0 THEN ROUND((T.P / T.O) * 100, 2) || '%' ELSE '0.00%' END , "
							+ "CASE WHEN T.R > 0 THEN T.R ELSE 0 END , CASE WHEN T.S > 0 THEN ROUND(T.S / 100, 2) ELSE 0.00 END , "
							+ "CASE WHEN T.R > 0 THEN ROUND((T.R / T.O) * 100, 2) || '%' ELSE '0.00%' END , "
							+ "CASE WHEN T.U > 0 THEN T.U ELSE 0 END , "
							+ "CASE WHEN T.U > 0 THEN ROUND((T.U / T.O) * 100, 2) || '%' ELSE '0.00%' END , "
							+ "CASE WHEN T.AE > 0 THEN T.AE ELSE 0 END , "
							+ "CASE WHEN T.AF > 0 THEN T.AF ELSE 0 END , "
							+ "CASE WHEN T.AG > 0 THEN T.AG ELSE 0 END , "
							+ "CASE WHEN T.AH > 0 THEN T.AH ELSE 0 END , "
							+ "CASE WHEN T.AH > 0 THEN ROUND((T.AG / T.AH) * 100, 2) || '%' ELSE '0%' END , "
							+ "T.AJ,T.AI, CASE WHEN T.AI > 0 THEN ROUND((T.AJ / T.AI) * 100, 2) || '%' ELSE '0%' END  "
							+ "FROM (SELECT * FROM (SELECT * FROM (SELECT A.MCHNT_CD AS T1_MCHT_CD,A.TRANS_DT AS T1_TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD AS T1_ROUT_INDUSTRY_INS_ID_CD, "
							+ "SUM(CASE WHEN A.CARD_CLASS <> '01' THEN 1 ELSE 0 END) M, "
							+ "SUM(CASE WHEN A.POS_ENTRY_MD_CD LIKE '02%' THEN 1 ELSE 0 END) N, "
							+ "SUM(CASE WHEN A.RESP_CD IN ('33','34','35','36','41','43','51','54','55','57','59','60','61','62','67','75') THEN 1 ELSE 0 END) P, "
							+ "SUM(CASE WHEN '1' = '1' THEN 1 ELSE 0 END) O, "
							+ "SUM(CASE WHEN (A.RESP_CD = '00' AND A.TRANS_ST = '1000000000') THEN 1 ELSE 0 END) R, "
							+ "SUM(CASE WHEN (A.RESP_CD <> '00') THEN 1 ELSE 0 END) U, "
							+ "SUM(CASE WHEN (A.RESP_CD = '00' AND A.TRANS_ST = '1000000000') THEN to_char(A.TRANS_AT) ELSE '0' END) S, "
							+ "SUM(CASE WHEN ((A.TRANS_MT BETWEEN '000000' AND '060000') AND A.TRANS_ST = '1000000000') THEN 1 ELSE 0 END) AE, "
							+ "SUM(CASE WHEN ((A.TRANS_MT BETWEEN '000000' AND '060000') AND A.TRANS_ST = '1000000000') THEN to_char(A.TRANS_AT) ELSE '0' END) AF, "
							+ "SUM(CASE WHEN ((A.TRANS_MT BETWEEN '000000' AND '060000') AND A.RESP_CD <> '00') THEN 1 ELSE 0 END) AG, "
							+ "SUM(CASE WHEN (A.TRANS_MT BETWEEN '000000' AND '060000') THEN 1 ELSE 0 END) AH FROM ";
					StringBuffer sb = new StringBuffer(sql);
					sb.append("hbat.tbl_tfr_trn_log ");
					params.add(startDT);
					sb.append("A WHERE A.TRANS_DT = ? AND A.INDUSTRY_INS_ID_CD = '48502000' AND A.MA_TRANS_CD IN ('1011', '1171') ");
					if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
						sb.append("and a.MCHNT_CD = ? ");
						params.add(mchtCd);
					}
					sb.append(
							"GROUP BY A.MCHNT_CD,A.TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD) T1 ")
							.append("FULL OUTER JOIN (SELECT A.MCHNT_CD AS T2_MCHT_CD,A.TRANS_DT AS T2_TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD AS T2_ROUT_INDUSTRY_INS_ID_CD, ")
							.append("SUM(CASE WHEN (A.RESP_CD <> '00') THEN 1 ELSE 0 END) AJ,SUM(CASE WHEN '1' = '1' THEN 1 ELSE 0 END) AI FROM ");
					sb.append("hbat.tbl_tfr_trn_log ");
					params.add(startDT);
					sb.append("A  WHERE A.TRANS_DT = ? AND A.INDUSTRY_INS_ID_CD = '48502000' AND A.MA_TRANS_CD IN ('1001') ");
					if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
						sb.append("and a.MCHNT_CD = ? ");
						params.add(mchtCd);
					}
					sb.append(
							"GROUP BY A.MCHNT_CD,A.TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD) T2 ON T1.T1_MCHT_CD = T2.T2_MCHT_CD) T3 LEFT JOIN APSTFR.TBL_MCHT_INF B ")
							.append("ON (T3.T1_MCHT_CD = B.MCHT_CD OR T3.T2_MCHT_CD = B.MCHT_CD)) T, APSMGM.T_OPERATORS T5 WHERE T.EXT1 = T5.ID ");
					if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
						sb.append("AND t.mcht_cd = ? ");
						params.add(mchtCd);
					}
					sb.append("order by t.mcht_cd ");
					sb2=sb;
			}
			List<Object> searchRsList=new ArrayList<Object>();
			searchRsList = pageDao.findAllByParams(sb2.toString(), params, startRow, maxRow);
			logger.info("查询到记录："+searchRsList.size()+"条");
			dealTheListSpecially(searchRsList,accountingList);
		} catch (Exception e) {
			logger.error("占比统计异常：",e);
		}
		return accountingList;
	}

	@Transactional(propagation=Propagation.REQUIRED,readOnly=true)
	@Override
	public int queryAccountStatisticsCount(String startDT,String mchtCd,String endDT,String dateFlag) throws ServiceException {
		List<Object> params = new ArrayList<Object>();
		int count = 0;
		try {
			if(!"1".equals(dateFlag) && !"2".equals(dateFlag)){
				logger.info("占比统计日期标志位值错误");
				return 0;
			}
			StringBuffer sb2 = new StringBuffer();
			String sql="";
			if(!"1".equals(dateFlag)){
		    sql = "select count(1) from TBL_ACCOUNTING_STATISTICS_INFO t where t.trans_dt >= ? and t.trans_dt<= ?";
			StringBuffer sb1 = new StringBuffer(sql);
			params.add(startDT);
			params.add(endDT);
			if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
				sb1.append("and t.MCHNT_CD =? ");
				params.add(mchtCd);
			}
			sb2=sb1;
			}
			else{
				sql = "SELECT count(1) FROM (SELECT * FROM (SELECT * FROM (SELECT A.MCHNT_CD AS T1_MCHT_CD,A.TRANS_DT AS T1_TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD AS T1_ROUT_INDUSTRY_INS_ID_CD, "
						+ "SUM(CASE WHEN A.CARD_CLASS <> '01' THEN 1 ELSE 0 END) M, "
						+ "SUM(CASE WHEN A.POS_ENTRY_MD_CD LIKE '02%' THEN 1 ELSE 0 END) N, "
						+ "SUM(CASE WHEN A.RESP_CD IN ('33','34','35','36','41','43','51','54','55','57','59','60','61','62','67','75') THEN 1 ELSE 0 END) P, "
						+ "SUM(CASE WHEN '1' = '1' THEN 1 ELSE 0 END) O, "
						+ "SUM(CASE WHEN (A.RESP_CD = '00' AND A.TRANS_ST = '1000000000') THEN 1 ELSE 0 END) R, "
						+ "SUM(CASE WHEN (A.RESP_CD <> '00') THEN 1 ELSE 0 END) U, "
						+ "SUM(CASE WHEN (A.RESP_CD = '00' AND A.TRANS_ST = '1000000000') THEN to_char(A.TRANS_AT) ELSE '0' END) S, "
						+ "SUM(CASE WHEN ((A.TRANS_MT BETWEEN '000000' AND '060000') AND A.TRANS_ST = '1000000000') THEN 1 ELSE 0 END) AE, "
						+ "SUM(CASE WHEN ((A.TRANS_MT BETWEEN '000000' AND '060000') AND A.TRANS_ST = '1000000000') THEN to_char(A.TRANS_AT) ELSE '0' END) AF, "
						+ "SUM(CASE WHEN ((A.TRANS_MT BETWEEN '000000' AND '060000') AND A.RESP_CD <> '00') THEN 1 ELSE 0 END) AG, "
						+ "SUM(CASE WHEN (A.TRANS_MT BETWEEN '000000' AND '060000') THEN 1 ELSE 0 END) AH FROM ";
				StringBuffer sb = new StringBuffer(sql);
				sb.append("hbat.tbl_tfr_trn_log ");
				
				sb.append("A WHERE A.TRANS_DT =? AND A.INDUSTRY_INS_ID_CD = '48502000' AND A.MA_TRANS_CD IN ('1011', '1171') ");
				params.add(startDT);
				if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
					sb.append("and a.MCHNT_CD =? ");
					params.add(mchtCd);
				}
				sb.append(
						"GROUP BY A.MCHNT_CD,A.TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD) T1 ")
						.append("FULL OUTER JOIN (SELECT A.MCHNT_CD AS T2_MCHT_CD,A.TRANS_DT AS T2_TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD AS T2_ROUT_INDUSTRY_INS_ID_CD, ")
						.append("SUM(CASE WHEN (A.RESP_CD <> '00') THEN 1 ELSE 0 END) AJ,SUM(CASE WHEN '1' = '1' THEN 1 ELSE 0 END) AI FROM ");
					sb.append("hbat.tbl_tfr_trn_log ");
				
				sb.append("A  WHERE A.TRANS_DT =? AND A.INDUSTRY_INS_ID_CD = '48502000' AND A.MA_TRANS_CD IN ('1001') ");
				params.add(startDT);
				if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
					sb.append("and a.MCHNT_CD =? ");
					params.add(mchtCd);
				}
				sb.append(
						"GROUP BY A.MCHNT_CD,A.TRANS_DT,A.ROUT_INDUSTRY_INS_ID_CD) T2 ON T1.T1_MCHT_CD = T2.T2_MCHT_CD) T3 LEFT JOIN APSTFR.TBL_MCHT_INF B ")
						.append("ON (T3.T1_MCHT_CD = B.MCHT_CD OR T3.T2_MCHT_CD = B.MCHT_CD)) T, APSMGM.T_OPERATORS T5 WHERE T.EXT1 = T5.ID ");
				if (!"".equals(Constance.Null2EmptyTrim(mchtCd))) {
					sb.append("AND t.mcht_cd =? ");
					params.add(mchtCd);
				}
				sb2=sb;
			}
			count = pageDao.countByParams(sb2.toString(),params);
			logger.info("占比统计条数查询sql结果："+count);
		} catch (Exception e) {
			logger.error("占比统计条数查询异常:",e);
		}
		return count;
	}
	
	
	private void dealTheListSpecially(List<Object> searchRsList,
			List<AccountingStatistics> accountingList) {
		if(searchRsList != null && searchRsList.size()>0){
			Iterator it = searchRsList.iterator();
			while(it.hasNext()){
				Object[] obj = (Object[]) it.next();
				AccountingStatistics as = new AccountingStatistics();
				as.setTransDt(Constance.Null2EmptyTrim(obj[0]));
				as.setRoutIndustryInsIdCd(Constance.Null2EmptyTrim(obj[1]));
				as.setExt1Name(Constance.Null2EmptyTrim(obj[2]));
				as.setProvCd(Constance.Null2EmptyTrim(obj[3]));
				as.setCityCd(Constance.Null2EmptyTrim(obj[4]));
				as.setMchtCd(Constance.Null2EmptyTrim(obj[5]));
				as.setApplDate(Constance.Null2EmptyTrim(obj[6]));
				as.setStatus(Constance.Null2EmptyTrim(obj[7]));
				as.setCreditAccounted(Constance.Null2EmptyTrim(obj[8]));
				as.setBrushAccounted(Constance.Null2EmptyTrim(obj[9]));
				as.setBusinessCount(Constance.Null2EmptyTrim(obj[10]));
				as.setHighReturnCount(Constance.Null2EmptyTrim(obj[11]));
				as.setHighRiskReturnAccount(Constance.Null2EmptyTrim(obj[12]));
				as.setTotalCount(Constance.Null2EmptyTrim(obj[13]));
				as.setTotalAmount(Constance.Null2EmptyTrim(obj[14]));
				as.setTotalSuccessAccount(Constance.Null2EmptyTrim(obj[15]));
				as.setTotalSuccessCount(Constance.Null2EmptyTrim(obj[16]));
				as.setTotalFailedAccount(Constance.Null2EmptyTrim(obj[17]));
				as.setNightSuccessCount(Constance.Null2EmptyTrim(obj[18]));
				as.setNightSuccessAmt(Constance.Null2EmptyTrim(obj[19]));
				as.setNightFailedCount(Constance.Null2EmptyTrim(obj[20]));
				as.setNightCount(Constance.Null2EmptyTrim(obj[21]));
				as.setNightFailedAccount(Constance.Null2EmptyTrim(obj[22]));
				as.setBalanceQueryFailedCount(Constance.Null2EmptyTrim(obj[23]));
				as.setBalanceQueryCount(Constance.Null2EmptyTrim(obj[24]));
				as.setBalanceQueryFailedAccount(Constance.Null2EmptyTrim(obj[25]));
				accountingList.add(as);
			}
		}
	}

}
